11 - Spatial and Temporal Data Management

Answers to exercises

:::

::::::::::::::::::: {style=“text-align:left”} ::: question 1.

What circumstances will lead to increased use of spatial data? :::

As customers expectations for location-based services grow, firms will need to add spatial information to the databases. For example, increasingly tourists will expect to be able to use their mobile phone to locate services near to their current locations (e.g., Where is the nearest Italian restaurant?).

3.

Using the following map of the Iberian peninsular, populate the spatial database with details of Andorra, Portugal, and Spain. Answer the following questions. 

INSERT INTO political_unit VALUES ('Spain', 'es', 4.9);
INSERT INTO political_unit VALUES ('Portugal','pt', 10);
INSERT INTO political_unit VALUES ('Andorra','ad', .067);
INSERT INTO political_unit VALUES ('United Kingdom','uk', 58.79);

INSERT INTO boundary VALUES (3,ST_GeomFromText('POLYGON((12 12, 13 12, 13 11, 12 11, 12 12))'),'ad');
INSERT INTO boundary VALUES (4,ST_GeomFromText('POLYGON((1 10, 4 10, 4 9, 3 3, 1 3, 1 5, 0 5, 0 6, 1 8, 1 10))'),'pt');
INSERT INTO boundary VALUES (5,ST_GeomFromText('POLYGON((1 12, 3 13, 12 12, 12 11, 15 11, 15 11, 11 7, 11 5, 9 2, 5 1, 4 1, 3 3, 4 9, 4 10, 1 10, 1 12))'),'es');

INSERT INTO city VALUES ('Braga',ST_GeomFromText('POINT(2 10)'),'pt');
INSERT INTO city VALUES ('Porto',ST_GeomFromText('POINT(1 9)'),'pt');
INSERT INTO city VALUES ('Lisbon',ST_GeomFromText('POINT(1 5)'),'pt');
INSERT INTO city VALUES ('Santiago de Compostela',ST_GeomFromText('POINT(2 12)'),'es');
INSERT INTO city VALUES ('Oviedo',ST_GeomFromText('POINT(5 12)'),'es');
INSERT INTO city VALUES ('Santander',ST_GeomFromText('POINT(7 12)'),'es');
INSERT INTO city VALUES ('Vitori',ST_GeomFromText('POINT(8 11)'),'es');
INSERT INTO city VALUES ('Pamplona',ST_GeomFromText('POINT(9 11)'),'es');
INSERT INTO city VALUES ('Logrono',ST_GeomFromText('POINT(8 11)'),'es');
INSERT INTO city VALUES ('Valiadoti',ST_GeomFromText('POINT(6 10)'),'es');
INSERT INTO city VALUES ('Saragossa',ST_GeomFromText('POINT(10 10)'),'es');
INSERT INTO city VALUES ('Barcelona',ST_GeomFromText('POINT(13 10)'),'es');
INSERT INTO city VALUES ('Madrid',ST_GeomFromText('POINT(7 8)'),'es');
INSERT INTO city VALUES ('Toledo',ST_GeomFromText('POINT(7 7)'),'es');
INSERT INTO city VALUES ('Valencia',ST_GeomFromText('POINT(11 6)'),'es');
INSERT INTO city VALUES ('Murcia',ST_GeomFromText('POINT(10 4)'),'es');
INSERT INTO city VALUES ('Sevilla',ST_GeomFromText('POINT(4 3)'),'es');
INSERT INTO city VALUES ('Gibralta',ST_GeomFromText('POINT(5 1)'),'uk');
INSERT INTO city VALUES ('Andorra la Veila',ST_GeomFromText('POINT(12 11)'),'ad');

Notes:

  • Gibraltar belongs to the United Kingdom
  • One unit on the grid is about 75 kms

3a.

What is the direct distance, or bee line, from Lisbon to Madrid?

SELECT ST_Distance(orig.cityloc, dest.cityloc)*75 as "Distance (kms)"
    FROM city orig, city dest
       WHERE orig.cityname = 'Lisbon'
      AND dest.cityname = 'Madrid';

3c.

Imagine you get lost in Portugal and your geographic positioning system (GPS) indicates that your coordinates are (3,9). What is the nearest city?

SELECT cityname FROM  city 
    WHERE  ST_Distance(cityloc, ST_GeomFromText('POINT(3 9)'))
      = (SELECT MIN(ST_Distance(cityloc, ST_GeomFromText('POINT(3 9)'))) FROM city);

3e.

What is the area of Portugal?

SELECT ST_AREA(boundpath)*5625 as "Area (km^2)" from political_unit, boundary
                    WHERE unitname = 'Portugal'
     AND political_unit.unitcode = boundary.unitcode;

5.

How would you compute distance if your relational database does not have a distance function?

You could need to use the Euclidean distance if the distance covered is not large (i.e., assume the earth is flat for distances of a few hundred kilometers or miles)

Distance = sqrt((x2 - x1)2 + (y2-y1)2)

7.

When might you use transaction time and when valid time?

  • Transaction time is used when you want to record when an event occurred.
  • Valid time is used to specify when an item's value becomes valid (e.g., a press release).

9.

A supermarket chain has asked you to record what goods customers buy during each visit. In other words, you want details of each shopping basket. It also wants to know when each purchase was made. Design the database.